package db

import (
	"errors"
	"fmt"
	"reflect"
	"strconv"
	"strings"
)

func snakeString(s string) string {
	sLen := len(s)
	data := make([]byte, 0, sLen*2)
	for i := 0; i < sLen; i++ {
		if s[i] >= 'A' && s[i] <= 'Z' {
			data = append(data, '_', s[i]+32)
		} else {
			data = append(data, s[i])
		}
	}

	return string(data)
}

func (s *Mysql) getSelectSql() string {
	var selectSql string

	if len(s.fields) == 0 {
		selectSql = "SELECT *"
	} else {
		selectSql = "SELECT `" + strings.Join(s.fields, "`, `") + "`"
	}

	return selectSql
}

func (s *Mysql) getWhereSql() string {
	var whereSql string

	if len(s.wheres) == 0 {
		return whereSql
	}

	for i, where := range s.wheres {
		var key, value, connector, op string
		valueReflect := reflect.ValueOf(where.value)
		switch where.op {
		case "=":
			switch valueReflect.Kind() {
			case reflect.Int, reflect.String:
				op = where.op
				if where.isNot {
					op = "!="
				}
				key = where.key
				value = "?"
				connector = where.connector

				s.args = append(s.args, where.value)
				break
			case reflect.Slice:
				tmpWheres := make([]string, 0)
				arrayCount := valueReflect.Len()
				for i := 0; i < arrayCount; i++ {
					tmpWheres = append(tmpWheres, "?")
					if itemKind := valueReflect.Index(i).Kind(); itemKind == reflect.String {
						s.args = append(s.args, valueReflect.Index(i).String())
					} else if itemKind == reflect.Int {
						s.args = append(s.args, valueReflect.Index(i).Int())
					}
				}

				op = "IN"
				if where.isNot {
					op = "NOT IN"
				}
				value = "(" + strings.Join(tmpWheres, ", ") + ")"
				connector = where.connector
				key = where.key
			}
			break
		case "LIKE":
			op = "LIKE"
			if where.isNot {
				op = "NOT LIKE"
			}
			value = "?"
			connector = where.connector
			key = where.key
			s.args = append(s.args, where.value)
			break
		}

		if i != 0 {
			whereSql += " " + connector + (" `" + key + "` ") + op + " " + value
		} else {
			whereSql += " WHERE" + (" `" + key + "` ") + op + " " + value
		}
	}

	return whereSql
}

func (s *Mysql) getFromSql() (string, error) {
	if s.tableName == "" {
		return "", errors.New("No Table Name")
	} else {
		return " FROM `" + s.tableName + "`", nil
	}
}

func (s *Mysql) getOrderBySql() string {
	var orderBySql = ""
	if len(s.orderBy) > 0 {
		for _, order := range s.orderBy {
			orderBySql += order.key + " " + order.order + ", "
		}

		orderBySql = " ORDER BY " + orderBySql[:len(orderBySql)-2]
	}

	return orderBySql
}

func (s *Mysql) getLimitSql() string {
	var limitSql string
	if s.limit > 0 {
		if s.offset > 0 {
			limitSql = " LIMIT " + strconv.Itoa(s.offset) + ", " + strconv.Itoa(s.limit)
		} else {
			limitSql = " LIMIT " + strconv.Itoa(s.limit)
		}
	}

	return limitSql
}

func (s *Mysql) getDeleteSql() string {
	sqlStr := "DELETE FROM `%s` %s %s"

	return strings.Trim(fmt.Sprintf(sqlStr, s.tableName, s.getWhereSql(), s.getLimitSql()), " ")
}

func (s *Mysql) getUpdateSql(args map[string]interface{}) string {
	sqlStr := "UPDATE `%s` SET %s %s %s"
	var itemStrings []string
	for key, v := range args {
		key = snakeString(key)
		itemStrings = append(itemStrings, "`"+key+"` = ?")
		s.args = append(s.args, v)
	}

	return strings.Trim(fmt.Sprintf(sqlStr, s.tableName, strings.Join(itemStrings, " , "), s.getWhereSql(), s.getLimitSql()), " ")
}

func (s *Mysql) getInsertSql(args map[string]interface{}) string {
	sqlStr := "INSERT INTO `%s` (%s) VALUES (%s)"
	var fieldStrings []string
	var valueStrings []string
	for key, v := range args {
		key = snakeString(key)
		fieldStrings = append(fieldStrings, "`"+key+"`")
		valueStrings = append(valueStrings, "?")
		s.args = append(s.args, v)
	}

	return strings.Trim(fmt.Sprintf(sqlStr, s.tableName, strings.Join(fieldStrings, " , "), strings.Join(valueStrings, " , ")), " ")
}

func (s *Mysql) getBatchInsertSql(args []map[string]interface{}) string {
	sqlStr := "INSERT INTO `%s` (%s) VALUES %s"

	var fieldStrings []string
	var valueStrings []string
	var fields []string
	for key := range args[0] {
		key = snakeString(key)
		fields = append(fields, key)
		fieldStrings = append(fieldStrings, "`"+key+"`")
	}

	for _, row := range args {

		var tmpValueStrings []string
		for _, key := range fields {
			tmpValueStrings = append(tmpValueStrings, "?")
			s.args = append(s.args, row[key])
		}

		valueStrings = append(valueStrings, "("+strings.Join(tmpValueStrings, ",")+")")
	}

	return fmt.Sprintf(sqlStr,
		s.tableName,
		strings.Join(fieldStrings, ","),
		strings.Join(valueStrings, ","))
}

func (s *Mysql) getQuerySql() (string, error) {
	sqlStr := s.getSelectSql()
	tmpFrom, err := s.getFromSql()
	if err != nil {
		return "", err
	}

	sqlStr += tmpFrom
	sqlStr += s.getWhereSql()
	sqlStr += s.getOrderBySql()
	sqlStr += s.getLimitSql()

	return sqlStr, nil
}
